﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTGroup_ZheJiang.Models;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
using DTGroup_ZheJiang.IDAL;

namespace DTGroup_ZheJiang.OracleDataAccess
{
    public class SenseStatisticsDao : ISenseStatistics
    {
        public string connString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
        /// <summary>
        /// 分页显示场景级别统计
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="sqlCondition"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public DataTable GetSenseStatisticsByPage(string tableName, int PageIndex, int PageSize, string sqlCondition, out int TotalCount)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("P_Day", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("Sense_Name", typeof(string));
            dt.Columns.Add("Point_Name", typeof(string));
            dt.Columns.Add("Longitude", typeof(string));
            dt.Columns.Add("Latitude", typeof(string));
            dt.Columns.Add("BuildingsCount", typeof(string));
            dt.Columns.Add("BuildingsMRCount", typeof(string));
            dt.Columns.Add("BuildingsWeakMRCount", typeof(string));
            dt.Columns.Add("BuildingsCoverPercent", typeof(string));
            dt.Columns.Add("BuildingsAvgRsrp", typeof(string));
            dt.Columns.Add("OutMRCount", typeof(string));
            dt.Columns.Add("OutWeakMRCount", typeof(string));
            dt.Columns.Add("OutCoverPercent", typeof(string));
            dt.Columns.Add("OutAvgRSRP", typeof(string));
            dt.Columns.Add("SenseId", typeof(string));
            string sqlText = @"With CET_10 AS ( 
	Select ROW_NUMBER()Over(order by P_Day desc)  RowNumber ,P_Day,City,Sense_Name,Point_Name,Longitude,Latitude,
BuildingsCount,BuildingsMRCount,BuildingsWeakMRCount,BuildingsCoverPercent,BuildingsAvgRsrp,OutMRCount,OutWeakMRCount,
OutCoverPercent,OutAvgRSRP,SenseId from " + tableName + sqlCondition + @"
            ) Select * From CET_10 Where RowNumber  
Between (case " + PageIndex + @" when 1 then " + PageSize * (PageIndex - 1) + @" else " + (PageSize * (PageIndex - 1) + 1) + @" 
end) And " + PageSize * PageIndex + @"";
            string sqlCount = "select count(0) from " + tableName + sqlCondition;
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlText;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 60 * 12 * 60;
                    using (OracleDataReader odr = cmd.ExecuteReader())
                    {
                        while (odr.Read())
                        {
                            dt.Rows.Add(
                                odr["P_Day"].ToString(),
                                odr["City"].ToString(),
                                odr["Sense_Name"].ToString(),
                                odr["Point_Name"].ToString(),
                                odr["Longitude"].ToString(),
                                odr["Latitude"].ToString(),
                                odr["BuildingsCount"].ToString(),
                                odr["BuildingsMRCount"].ToString(),
                                odr["BuildingsWeakMRCount"].ToString(),
                                odr["BuildingsCoverPercent"].ToString(),
                                odr["BuildingsAvgRsrp"].ToString(),
                                odr["OutMRCount"].ToString(),
                                odr["OutWeakMRCount"].ToString(),
                                odr["OutCoverPercent"].ToString(),
                                odr["OutAvgRSRP"].ToString(),
                                odr["SenseId"].ToString()
                                );
                        }
                    }
                }
                conn.Close();
                using (OracleCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 12 * 60 * 60;
                    command.CommandText = sqlCount;
                    TotalCount = Convert.ToInt32(command.ExecuteScalar());
                }
                conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// 查询结果导出
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public List<SenseStatistics> GetSenseStatisticsByWhere(string tableName, string sqlCondition)
        {
            List<SenseStatistics> list = new List<SenseStatistics>();
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from " + tableName + sqlCondition;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 60 * 12 * 60;
                    using (OracleDataReader odr = cmd.ExecuteReader())
                    {
                        while (odr.Read())
                        {
                            SenseStatistics ss = new SenseStatistics
                            {
                                P_Day = Convert.ToDateTime(odr["P_Day"]),
                                City = odr["City"].ToString(),
                                Sense_Name = odr["Sense_Name"].ToString(),
                                Point_Name = odr["Point_Name"].ToString(),
                                Longitude = Convert.ToDouble(odr["Longitude"]),
                                Latitude = Convert.ToDouble(odr["Latitude"]),
                                BuildingsCount = Convert.ToInt32(odr["BuildingsCount"]),
                                BuildingsMRCount = Convert.ToInt32(odr["BuildingsMRCount"]),
                                BuildingsWeakMRCount = Convert.ToInt32(odr["BuildingsWeakMRCount"]),
                                BuildingsCoverPercent = Convert.ToDouble(odr["BuildingsCoverPercent"]),
                                BuildingsAvgRsrp = Convert.ToDouble(odr["BuildingsAvgRsrp"]),
                                OutMRCount = Convert.ToInt32(odr["OutMRCount"]),
                                OutWeakMRCount = Convert.ToInt32(odr["OutWeakMRCount"]),
                                OutCoverPercent = Convert.ToDouble(odr["OutCoverPercent"]),
                                OutAvgRSRP = Convert.ToDouble(odr["OutAvgRSRP"]),
                                SenseId = Convert.ToInt32(odr["SenseId"])
                            };
                            list.Add(ss);
                        }
                    }
                }
            }
            return list;
        }
    }
}
